//
//  DBUtils.swift
//
//
//  Created by lzh on 2022/5/31.
//

import Foundation
import SQLite

let id_column = Expression<UUID>("id")
let title_column=Expression<String>("title")
let content_column=Expression<String>("content")
let create_time_stamp_column=Expression<String>("createTimeStamp")
let update_time_stamp_column=Expression<String>("updateTimeStamp")

class NoteDBUtils: ObservableObject {
    @Published var noteList:[NoteModel]=[]
    static let single = NoteDBUtils()
    
    private var db:Connection?
    private var notesTable:Table?
    private var searchContent:String=""
    private var orderbyIndex:Int=0;
    init() {
    getNotes(keyWord: searchContent, orderby: orderbyIndex)
    }
    func getDB() -> Connection {
           if db == nil {
               let path = NSSearchPathForDirectoriesInDomains(
                   .documentDirectory, .userDomainMask, true
                   ).first!
               db = try! Connection("\(path)/db.sqlite3")
               db?.busyTimeout = 5.0
           }
           return db!
       }
    func getNoteTable() -> Table {
            if notesTable == nil {
                notesTable = Table("note")
                try! getDB().run(
                    notesTable!.create (ifNotExists: true, block:{ t in
                        t.column(id_column, primaryKey: true)
                        t.column(title_column)
                        t.column(content_column)
                        t.column(create_time_stamp_column)
                        t.column(update_time_stamp_column)
                    })
                )
            }
            return notesTable!
        }
    
    // 增加
        func insert(note:NoteModel) -> Bool{
            let insert = getNoteTable().insert(id_column<-note.id,title_column <- note.title,content_column<-note.content,create_time_stamp_column<-note.createTimeStamp,update_time_stamp_column<-note.updateTimeStamp)
            if let rowId = try? getDB().run(insert) {
                print("插入成功：\(rowId)条记录")
                getNotes(keyWord: searchContent, orderby: orderbyIndex)
                return true
            } else {
                print("插入失败")
                return false
            }
        }
    // 查询
    func getNotes(keyWord:String,orderby:Int) -> Void{
        self.searchContent=keyWord
        self.orderbyIndex=orderby
        var orderby2:SQLite.Expressible
        switch orderby {
        case 0:
            orderby2=create_time_stamp_column.desc
        case 1:
            orderby2=update_time_stamp_column.desc
        default:
            orderby2=title_column.desc
        }
        let query = getNoteTable().order(orderby2).where(title_column.like("%\(keyWord)%")||content_column.like("%\(keyWord)%"))
            print(query)
            do {
                noteList.removeAll()
                let results = try getDB().prepare(query);
                
                for noteRow in results {
                    let note = NoteModel(id: noteRow[id_column], title: noteRow[title_column], content: noteRow[content_column], createTimeStamp: noteRow[create_time_stamp_column], updateTimeStamp: noteRow[update_time_stamp_column])
            
                    noteList.append(note)
                }
                print("查询成功")
                
            } catch {
                print("出错")
            }
        }
    
    func delete( note:NoteModel) -> Bool{
        let deleteCmd = getNoteTable().filter(Expression<UUID>("id") == note.id)
        if let count = try? getDB().run(deleteCmd.delete()) {
            return count>0
        
        } else {
            return false
        }
    }
    
    func update(note:NoteModel) -> Bool {
        let updateCmd=getNoteTable().filter(id_column==note.id).update(id_column<-note.id,title_column <- note.title,content_column<-note.content,update_time_stamp_column<-Date().milliStamp)
        print(updateCmd)
        if let rowId = try? getDB().run(updateCmd) {
            print("更新成功：\(rowId)条记录")
            
//            noteList[noteList.firstIndex(where: { (NoteModel) -> Bool in
//                NoteModel.id==note.id
//
//            }) ?? 0]=note
            getNotes(keyWord: searchContent, orderby: orderbyIndex)
            return true
        } else {
            print("更新失败")
            return false
        }
        
    }
}
